package com.mitac.mcd.cs.mobiletracking.socketserver.report;

import java.beans.PropertyVetoException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.Calendar;

public class WeeklyReportMaker {

	/**
	 * weekly statistic data and mail
	 * @throws ParseException 
	 */
	public static void StatisticMailWeekly() {
		java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	ResultSet rs = null;ResultSet current_rs = null;ResultSet last_rs = null;
    	StringBuffer sb = new StringBuffer();
    	
    	Calendar date = Calendar.getInstance();
    	date.add(Calendar.DAY_OF_YEAR, -1);//恢复到上个礼拜日
    	int total_count = 0;double total_percent=0.0;int total_count_last = 0;
    	int year = date.get(Calendar.YEAR);
    	int month = date.get(Calendar.MONTH)+1;
    	int day = date.get(Calendar.DAY_OF_MONTH);
    	
    	date.add(Calendar.DAY_OF_YEAR, -7);//上上个礼拜礼拜日
    	int l_year = date.get(Calendar.YEAR);
    	int l_month = date.get(Calendar.MONTH)+1;
    	int l_day = date.get(Calendar.DAY_OF_MONTH);
    	date.add(Calendar.DAY_OF_YEAR, 7);//回复到上个礼拜日
    	DecimalFormat df2 = new DecimalFormat("##.00");
    	/********************************exception************************************/
    	String sqlA = " select ep.EXCEPTION_PROP_CODE,sed.COUNT,sed.PERCENTOFWEEK,sed.TOTAL_COUNT,ep.NOTE "+
    					" from ( select * from SOCKETSERVER_EXCEPTIONLOG_WEEKLYREPORT sed " +
    					" where year(sed.LOG_TIME) = "+year+" and month(sed.LOG_TIME) = "+month+" and day(sed.LOG_TIME) = "+day+" ) " +
    					" sed right join " +
    					" EXCEPTIONPROPERTIES ep "+
    					" on sed.EXCEPTION_PROP_CODE = ep.EXCEPTION_PROP_CODE ";
    	
    	try {
    		dbc = DBConnection.getConnection("gpsdata");
    		
			BusinessUtil.aLogger.info("StatisticMailWeekly:"+sqlA);
			pst = dbc.prepareStatement(sqlA);
			current_rs = pst.executeQuery();
			
			String sqlC = " select ep.EXCEPTION_PROP_CODE,sed.COUNT,sed.PERCENTOFWEEK,sed.TOTAL_COUNT,ep.NOTE "+
			" from ( select * from SOCKETSERVER_EXCEPTIONLOG_WEEKLYREPORT sed " +
			" where year(sed.LOG_TIME) = "+l_year+" and month(sed.LOG_TIME) = "+l_month+" and day(sed.LOG_TIME) = "+l_day+" ) " +
			" sed right join EXCEPTIONPROPERTIES ep "+
			" on sed.EXCEPTION_PROP_CODE = ep.EXCEPTION_PROP_CODE ";
			BusinessUtil.aLogger.info("StatisticMailWeekly:"+sqlC);
			pst = dbc.prepareStatement(sqlC);
			last_rs = pst.executeQuery();
			while(last_rs.next()){
				total_count_last = total_count_last + last_rs.getInt("COUNT");
			}
			last_rs.beforeFirst();
			
			BusinessUtil.getFirstDayOfWeek(date);String firstday = Formatter.daily.format(date.getTime());
			BusinessUtil.getLastDayOfWeek(date);String lastday = Formatter.daily.format(date.getTime());
			sb.append("<p align=\"center\">");
			sb.append(MobileTrackingReport.ConfigProperties.getProperty("WEEKLY_REPORT_EXCEPTION_TITLE"));
			sb.append(" (");
			sb.append(firstday+"-"+lastday);
			sb.append(")</p>");
			sb.append("<hr width=\"50%\"/>");
			sb.append("<table width=\"50%\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\">");
			sb.append("<tr>");
			sb.append("<td width=\"20%\"><div align=\"center\">Error Code </div></td>");
			sb.append("<td width=\"10%\"><div align=\"center\">Count</div></td>");
			sb.append("<td width=\"20%\"><div align=\"center\">Comparison</div></td>");
			sb.append("<td width=\"10%\"><div align=\"center\">Percentage(%)</div></td>");
			sb.append("<td width=\"40%\"><div align=\"center\">Error Description </div></td>");
			sb.append("</tr>");
			while(current_rs.next()){
				String error_code_current = current_rs.getString("EXCEPTION_PROP_CODE");
				int current_count = current_rs.getInt("COUNT");
				int last_count=0;
				while(last_rs.next()){
					String error_code_last = last_rs.getString("EXCEPTION_PROP_CODE");
					if(error_code_last.equals(error_code_current)){
						last_count = last_rs.getInt("COUNT");
					}
				}
				last_rs.beforeFirst();
				
				sb.append("<tr>");
				sb.append("<td><div align=\"center\">").append(error_code_current).append("</div></td>");
				String icon = null;
				if (current_count>last_count){ icon = MobileTrackingReport.up_icon;}
				if (current_count<last_count){ icon = MobileTrackingReport.down_icon;}
				sb.append("<td><div align=\"right\">").append(current_count).append("</div></td>");
				sb.append("<td><div align=\"right\">").append(icon==null?"0":Math.abs(current_count-last_count)).append(icon==null?"":icon).append("</div></td>");
				total_percent = total_percent + current_rs.getDouble("PERCENTOFWEEK");
				sb.append("<td><div align=\"center\">").append(current_rs.getDouble("PERCENTOFWEEK")).append("</div></td>");
				sb.append("<td><div align=\"left\">").append(current_rs.getString("NOTE")).append("</div></td>");
				sb.append("</tr>");
				total_count = total_count + current_count;
			}
			sb.append("<tr>");
			sb.append("<td width=\"20%\"><div align=\"center\">Total</div></td>");
			String total_icon = null;
			if (total_count>total_count_last){ total_icon = MobileTrackingReport.up_icon;}
			if (total_count<total_count_last){ total_icon = MobileTrackingReport.down_icon;}
			sb.append("<td width=\"10%\"><div align=\"right\">").append(total_count).append("</div></td>");
			sb.append("<td width=\"20%\"><div align=\"right\">").append(total_icon == null?"0":Math.abs(total_count-total_count_last)).append(total_icon==null?"":total_icon).append("</div></td>");
			sb.append("<td width=\"10%\"><div align=\"center\">").append(df2.format(total_percent)).append("</div></td>");
			sb.append("<td width=\"40%\"><div align=\"center\"></div></td>");
			sb.append("</tr>");
			sb.append("</table>");
			
			/********************************notification************************************/
			String sqlD = " select RECEIVE_CMD_COUNT,RECEIVE_CMD_CLIENT_COUNT,RECEIVE_NEWEST_ORDER_COUNT,RECEIVE_NEWEST_ORDER_CLIENT_COUNT, "+
			" RECEIVE_NEWEST_MSG_COUNT,RECEIVE_NEWEST_MSG_CLIENT_COUNT,RECEIVE_UNREAD_ORDER_COUNT," +
			" RECEIVE_UNREAD_ORDER_CLIENT_COUNT, " +
			" RECEIVE_UNREAD_MSG_COUNT,RECEIVE_UNREAD_MSG_CLIENT_COUNT,SUCCESS_DELIVER_CLIENT,FAIL_DELIVER_CLIENT " +
			" from SOCKETSERVER_NOTIFICATION_WEEKLYREPORT "+
			" where year(LOG_TIME) = "+year+" and month(LOG_TIME) = "+month+" and day(LOG_TIME) = "+day;
			BusinessUtil.aLogger.info("StatisticMailWeekly:"+sqlD);
			pst = dbc.prepareStatement(sqlD);
			current_rs = pst.executeQuery();
			sb.append("<p align=\"center\">");
			sb.append(MobileTrackingReport.ConfigProperties.getProperty("WEEKLY_REPORT_NOTIFICATION_TITLE"));
			sb.append(" (");
			sb.append(firstday+"-"+lastday);
			sb.append(")</p>");
			sb.append("<hr width=\"50%\"/>");
			sb.append("<table width=\"50%\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\">");
			sb.append("<tr>");
			sb.append("<td width=\"50%\"><div align=\"center\">Item</div></td>");
			sb.append("<td width=\"30%\"><div align=\"center\">Command Count</div></td>");
			sb.append("<td width=\"20%\"><div align=\"center\">Client Count</div></td>");
			sb.append("</tr>");
			while(current_rs.next()){
				int receive_cmd_count = current_rs.getInt("RECEIVE_CMD_COUNT");
				int receive_cmd_client_count = current_rs.getInt("RECEIVE_CMD_CLIENT_COUNT");
				int receive_newest_order_count = current_rs.getInt("RECEIVE_NEWEST_ORDER_COUNT");
				int receive_newest_order_client_count = current_rs.getInt("RECEIVE_NEWEST_ORDER_CLIENT_COUNT");
				int receive_newest_msg_count = current_rs.getInt("RECEIVE_NEWEST_MSG_COUNT");
				int receive_newest_msg_client_count = current_rs.getInt("RECEIVE_NEWEST_MSG_CLIENT_COUNT");
				int receive_unread_order_count = current_rs.getInt("RECEIVE_UNREAD_ORDER_COUNT");
				int receive_unread_order_client_count = current_rs.getInt("RECEIVE_UNREAD_ORDER_CLIENT_COUNT");
				int receive_unread_msg_count = current_rs.getInt("RECEIVE_UNREAD_MSG_COUNT");
				int receive_unread_msg_client_count = current_rs.getInt("RECEIVE_UNREAD_MSG_CLIENT_COUNT");
				int success_deliver_client = current_rs.getInt("SUCCESS_DELIVER_CLIENT");
				int fail_deliver_client = current_rs.getInt("FAIL_DELIVER_CLIENT");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"left\">").append("Receive Cmds").append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_cmd_count).append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_cmd_client_count).append("</div></td>");
				sb.append("</tr>");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"left\">").append("Receive Newest Order Cmds").append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_newest_order_count).append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_newest_order_client_count).append("</div></td>");
				sb.append("</tr>");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"left\">").append("Receive Newest Message Cmds").append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_newest_msg_count).append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_newest_msg_client_count).append("</div></td>");
				sb.append("</tr>");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"left\">").append("Receive Unread Order Cmds").append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_unread_order_count).append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_unread_order_client_count).append("</div></td>");
				sb.append("</tr>");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"left\">").append("Receive Unread Message Cmds").append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_unread_msg_count).append("</div></td>");
				sb.append("<td><div align=\"right\">").append(receive_unread_msg_client_count).append("</div></td>");
				sb.append("</tr>");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"left\">").append("Success Deliver To Client").append("</div></td>");
				sb.append("<td><div align=\"right\">").append("none").append("</div></td>");
				sb.append("<td><div align=\"right\">").append(success_deliver_client).append("</div></td>");
				sb.append("</tr>");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"left\">").append("Fail Deliver To Client").append("</div></td>");
				sb.append("<td><div align=\"right\">").append("none").append("</div></td>");
				sb.append("<td><div align=\"right\">").append(fail_deliver_client).append("</div></td>");
				sb.append("</tr>");
			}
			sb.append("<tr>");
			sb.append("</table>");
			/********************************online count************************************/
			String sqlE = " select ONLINE_COUNT,LOG_TIME,SOCKET_SERVER_IP from ONLINE_USER_ANALYSIS sed "+
			" where sed.LOG_TIME >= '"+Formatter.compare.format(Formatter.daily.parse(firstday))+"'"+
			" and sed.LOG_TIME <= '"+Formatter.compare.format(Formatter.daily.parse(lastday))+" 24:00:00'"+
			" order by ONLINE_COUNT desc limit 0,5 ";
			BusinessUtil.aLogger.info("StatisticMailWeekly:"+sqlE);
			pst = dbc.prepareStatement(sqlE);
			current_rs = pst.executeQuery();
			sb.append("<p align=\"center\">");
			sb.append("Socket Server OnLine User Peak Rate Analysis (");
			date.add(Calendar.DAY_OF_YEAR, 1);
			sb.append(Formatter.daily.format(date.getTime()));
			sb.append(")</p>");
			sb.append("<table width=\"50%\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\">");
			sb.append("<tr>");
			sb.append("<td width=\"40%\"><div align=\"center\">Server IP</div></td>");
			sb.append("<td width=\"40%\"><div align=\"center\">Time</div></td>");
			sb.append("<td width=\"20%\"><div align=\"right\">Count</div></td>");
			sb.append("</tr>");
			while(current_rs.next()){
				int online_count = current_rs.getInt("ONLINE_COUNT");
				String logtime = current_rs.getString("LOG_TIME");
				String ip = current_rs.getString("SOCKET_SERVER_IP");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"center\">").append(ip).append("</div></td>");
				sb.append("<td><div align=\"center\">").append(logtime).append("</div></td>");
				sb.append("<td><div align=\"right\">").append(online_count).append("</div></td>");
				sb.append("</tr>");
			}
			sb.append("</table>");
			
			String sqlF = " select ONLINE_COUNT,LOG_TIME,SOCKET_SERVER_IP from ONLINE_USER_ANALYSIS sed "+
			" where sed.LOG_TIME >= '"+Formatter.compare.format(Formatter.daily.parse(firstday))+"'"+
			" and sed.LOG_TIME <= '"+Formatter.compare.format(Formatter.daily.parse(lastday))+" 24:00:00'"+
			" order by ONLINE_COUNT asc limit 0,5 ";
			BusinessUtil.aLogger.info("StatisticMailWeekly:"+sqlF);
			pst = dbc.prepareStatement(sqlF);
			current_rs = pst.executeQuery();
			sb.append("<p align=\"center\">");
			sb.append("Socket Server OnLine User Off-Peak Rate Analysis (");
			sb.append(Formatter.daily.format(date.getTime()));
			sb.append(")</p>");
			sb.append("<table width=\"50%\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\">");
			sb.append("<tr>");
			sb.append("<td width=\"40%\"><div align=\"center\">Server IP</div></td>");
			sb.append("<td width=\"40%\"><div align=\"center\">Time</div></td>");
			sb.append("<td width=\"20%\"><div align=\"right\">Count</div></td>");
			sb.append("</tr>");
			while(current_rs.next()){
				int online_count = current_rs.getInt("ONLINE_COUNT");
				String logtime = current_rs.getString("LOG_TIME");
				String ip = current_rs.getString("SOCKET_SERVER_IP");
				
				sb.append("<tr>");
				sb.append("<td><div align=\"center\">").append(ip).append("</div></td>");
				sb.append("<td><div align=\"center\">").append(logtime).append("</div></td>");
				sb.append("<td><div align=\"right\">").append(online_count).append("</div></td>");
				sb.append("</tr>");
			}
			sb.append("</table>");
			
			String title = MobileTrackingReport.ConfigProperties.getProperty("WEEKLY_MAIL_SUBJECT")+
							" "+"("+firstday+"-"+lastday+")";
			
			String tto = MobileTrackingReport.ConfigProperties.getProperty("WEEKLY_RECEIVER");
			SendHTMLMail.sendMail(title,sb.toString(),tto);
			
			if(rs!=null) rs.close();
			if(current_rs!=null) current_rs.close();
			if(last_rs!=null) last_rs.close();
			pst.close();
			dbc.close();
		} catch (SQLException e) {
			e.printStackTrace();
			BusinessUtil.aLogger.error("StatisticMailWeekly:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			BusinessUtil.aLogger.error("StatisticMailWeekly:"+e);
		}
    	
	}
}
